<?php
$server = "172.21.147.31";
$connectionInfo = array( "Database"=>"ss2g6", "UID"=>"ss2g6", "PWD"=>"group6", 'ReturnDatesAsStrings'=> true );
$conn = sqlsrv_connect( $server, $connectionInfo );

if ($conn == false){
	 die( print_r( sqlsrv_errors(), true));
}

if (isset($_POST['submit'])){
    $sql1 = $_POST['query'];
}
else
{
    $sql = "SELECT d1.*
			FROM Doctor d1 
			WHERE d1.doctorId IN
				(SELECT p1.doctorId 
				 FROM Prescribes p1
				 GROUP BY p1.doctorId
				 HAVING (COUNT(*) / 2) <= 
					(SELECT COUNT(doctorId) 
					 FROM Prescribes p2 
					 JOIN HasSideEffect h2
					 ON p2.companyName = h2.companyName 
						AND p2.tradeName = h2.tradeName
					 WHERE h2.name='Drowsiness' 
						   AND p1.doctorId = p2.doctorId
					 GROUP BY doctorId))";
}
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );
?>


<? include 'html_head.php' ?>
    <? include 'header.php' ?>

    <div class="container-fluid">
      <div class="row-fluid">
        <div class="span2">
			<? include 'sidebar.php' ?>
        </div><!--/span-->
        <div class="span9">
		<? if(isset($_POST['Insert'])){ ?>
                <div class="alert alert-success">
                <? echo $confirm; ?>
                </div>
		<?
        }
		if(sqlsrv_has_rows($stmt))
		{		
            $row_count = sqlsrv_num_rows($stmt);
            $colnames = sqlsrv_field_metadata($stmt)
		?>
		<h3>
			Query 6: 
			</br>List the doctors whose at least half of the prescribed drugs have "drowsiness" as side effect.
		</h3>
		</br>
		<table class="table table-striped table table-condensed">
		
		<thead>
			<tr>
			   <?
					echo "<th>Doctor ID</th>";
					echo "<th>Years of Experience</th>";
					echo "<th>Name</th>";
					echo "<th>Specialty</th>";
                ?>
			</tr>	
			</thead>
		<tbody>
		<?
			while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
		?>
			<tr>
				<?
					foreach ($colnames as &$colname){
					$cellname = $colname['Name'];
					echo "<td>";
					if ($colname['Type'] == 93){
						$temp_date = explode(" ", $row[$cellname]);
						echo $temp_date[0];	
					}
					else{
						if($cellname == "specialty"){
							if ($row['specialty'] == ""){
								echo "No specialty";
							}
							else{
								echo $row['specialty'];
							}
						}
						else{
							echo $row[$cellname];
						}
					}	
					echo "</td>";
					}						
				?>
			</tr>
		    
		<?
			//echo "</td>";
			}
		//}
		?>

		</tbody>
		</table>
		<?
		}
		else{
			echo "<h2>No records.</h2>";
		}
		?>
          <div class="row-fluid">
            <div class="span12">
               <h3>Enter your query:</h3>
                    <form method="post" action="custom.php" class="well form-inline">
                    <div class="control-group">
                        <div class="controls">
                            <textarea name="query" class="span10" id="textarea" rows="5" placeholder = 'SQL query'></textarea>
                        </div>
                    </div>
                    <p>
                    <p><input type="submit" name="submit" class="btn btn-primary" href="#" /></p>
                    </form>
                    
            </div><!--/span-->
          </div><!--/row-->
        </div><!--/span-->
      </div><!--/row-->

      <hr>

      <footer>
        <p>&copy; SS2 GROUP 6 </p>
      </footer>

    </div><!--/.fluid-container-->
  </body>
</html>